package de.hmm.hwip.configuration.system;

import de.hmm.hwip.configuration.api.ISystemConf;


/**
 *  Pojo to get querys to get or set System Datas
 * 
 * @author Johannes Mendel
 * @version 28.10.2012
 *
 */
public class SystemConfImpl implements ISystemConf{

	/**
	 * function to get the query for getting all floors
	 * 
	 * @return query to get all data from floor table
	 */
	@Override
	public String getQueryForFloors(){
		String query = "";
		
		query = "SELECT * FROM floor;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all rooms
	 * 
	 * @return query to get all data from room table
	 */
	@Override
	public String getQueryForRooms(){
		String query = "";
		
		query = "SELECT * FROM room;";
		
		return query;
	}
	/**
	 * function to get the query for getting all spots
	 * 
	 * @return query to get all data from spot table
	 */
	@Override
	public String getQueryForSpots(){
		String query = "";
		
		query = "SELECT * FROM spot;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all radiotypes
	 * 
	 * @return query to get all data from radiotype table
	 */
	@Override
	public String getQueryForRadiotypes(){
		String query = "";
		
		query = "SELECT * FROM radiotype;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all devices
	 * 
	 * @return query to get all data from device table
	 */
	@Override
	public String getQueryForDevices(){
		String query = "";
		
		query = "SELECT * FROM device;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all system devices
	 * 
	 * @return query to get all data from sys_device table
	 */
	@Override
	public String getQueryForSysDevices(){
		String query = "";
		
		query = "SELECT * FROM sys_device;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all controlscripts
	 * 
	 * @return query to get all data from controlscript table
	 */
	@Override
	public String getQueryForControlscripts(){
		String query = "";
		
		query = "SELECT * FROM controlscript;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all linked device commands
	 * 
	 * @return query to get all data from link_device_command table
	 */
	@Override
	public String getQueryForLinkDevCommands(){
		String query = "";
		
		//query = "SELECT * FROM ;";#
		
		return query;
	}
	
	/**
	 * function to get the query for getting all commands
	 * 
	 * @return query to get all data from command table
	 */
	@Override
	public String getQueryForCommands(){
		String query = "";
		
		query = "SELECT * FROM command;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all connected devices
	 * 
	 * @return query to get all data from connected_devices table
	 */
	@Override
	public String getQueryForConnectedDevices(){
		String query = "";
		
		//query = "";#
		
		return query;
	}
	
	/**
	 * function to get the query to get all rooms for given floor
	 * 
	 * @param floorId : foreign key from table floor
	 * @return query to get rooms for given floor
	 */
	@Override
	public String getQueryForRoomsByFk(int floorId){
		String query = "";
		
		query = "SELECT * FROM room WHERE floor_id_fk = " + floorId + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to get all spots for given room
	 * 
	 * @param room_id : foreign key from table room
	 * @return query to get spots for given room
	 */
	@Override
	public String getQueryForSpotsByFk(int room_id){
		String query = "";
		
		query = "SELECT * FROM spot WHERE room_id_fk = " + room_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to get all devices for given radiotype
	 * 
	 * @param rt_id : foreign key from table radiotype
	 * @return query to get devices for given radiotype
	 */
	@Override
	public String getQueryForDevicesByFk(int rt_id){
		String query = "";
		
		query = "SELECT * FROM device WHERE rt_id_fk = " + rt_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to get all system devices for given device id
	 * 
	 * @param device_id : foreign key from table device
	 * @return query to get system devices for given device id
	 */
	@Override
	public String getQueryForSysDevByDevFk(int device_id){
		String query = "";
		
		query = "SELECT * FROM sys_device WHERE device_id_fk = " + device_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to get all system devices for given spot id
	 * 
	 * @param spot_id : foreign key from table spot
	 * @return query to get system devices for given spot id
	 */
	@Override
	public String getQueryForSysDevBySpotFk(int spot_id){
		String query = "";
		
		query = "SELECT * FROM sys_device WHERE spot_id_fk = " + spot_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to get all commands for given device id
	 * 
	 * @param device_id : foreign key from table device
	 * @return query to get commands for given device id
	 */
	@Override
	public String getQueryForCommandByDevFk(int device_id){
		String query = "";
		
		query = "SELECT * FROM command AS c " +
				"JOIN link_device_command AS ldc ON c.command_id = ldc.command_id_fk " +
				"WHERE ldc.device_id_fk = " + device_id +" ;" ;
		
		return query;
	}
	
	/**
	 * function to get the query to get all control scripts for given system device id
	 * 
	 * @param sys_dev_id : foreign key from table sys_device
	 * @return query to get control scripts for given sys_dev_id
	 */
	@Override
	public String getQueryForControlBySysDevFk(int sys_dev_id){
		String query = "";
		
		query = "SELECT cs.cs_id, c.command_id, sd.sys_dev_id, c.command, c.attr, cs.value, cs.time, cs.nextscript, sd.sys_specific, sd.sn_addr " +
				"FROM controlscript AS cs " +
				"JOIN connected_devices AS cd ON cs.cs_id = cd.cs_id_fk " +
				"JOIN command AS c ON c.command_id = cs.command_id_fk " +
				"JOIN sys_device AS sd ON sd.sys_dev_id = cs.sys_dev_id_fk " +
				"WHERE cd.sys_dev_id_fk = " + sys_dev_id +" ;" ;
		
		return query;
	}
	
	
	/**
	 * function to get the query for setting new floor
	 * 
	 * @param id : primary key
	 * @param floorNum : Floor number e.g. -2 for 2nd cellar
	 * @param description : description of the floor e.g. 1st Floor
	 * @return query to set floor data
	 */
	@Override
	public String getQueryForSetFloor(int id, int floorNum, String description){
		String query = "";
		
		query = "INSERT INTO floor (floor_id, f_num, f_description) " +
				"VALUES ("+id + ", " + floorNum + ", '" + description + "');";
		
		return query;
	}
	/**
	 * function to get the query for setting new room
	 * 
	 * @param id : primary key
	 * @param floorId : foreign key from table Floor
	 * @param description : description of the room e.g. Office
	 * @return query to set room data
	 */
	@Override
	public String getQueryForSetRoom(int id, int floorId, String description){
		String query;
		
		query = "INSERT INTO room (room_id, floor_id_fk, r_description) " +
				"VALUES ("+ id + ", " + floorId + ", '" + description + "');"; 
		
		return query;
	}
	
	/**
	 * function to get the query for setting new radio type
	 * 
	 * @param id : primary key
	 * @param name : name of the radio type e.g. Ant
	 * @return query to set radio type
	 */
	@Override
	public String getQueryForSetRadioType(int id, String name){
		String query = "";
		
		query = "INSERT INTO radiotype (rt_id, rt_name) " +
				"VALUES ("+id + ", '" + name + "');";
		
		return query;
	}
	
	/**
	 * function to get the query for setting new device
	 * 
	 * @param id : primary key
	 * @param rt_id : foreign key from table radiotype
	 * @param model_type : type/name of device
	 * @param manufacture : manufacture of device
	 * @param specific : specification of device e.g Scale
	 * @param response : response for device if needed
	 * @param attr : attributes of device 
	 * @return query to set device
	 */
	@Override
	public String getQueryForSetDevice(int id, int rt_id, String model_type, String manufacture, String specific, String response, String[] attr){
		String query = "";
		
		query = "INSERT INTO device (device_id, rt_id_fk, model_type, manufacture, d_specific, response";
				for(int i = 0; i < attr.length ; i++){
					query += " , attr_" + (i+1);
				}
		
				query += ") VALUES ( "+id + ", " + rt_id + ", '" + model_type + "', '" + manufacture + "', '" + specific + "', '" + response + "' ";
				
				for(int i = 0; i < attr.length; i++){
					query += ", '" + attr[i] + "' ";
				}
				query += ");";
		
		return query;
	}
	
	/**
	 * function to get the query for linking a device with a command
	 * 
	 * @param dev_id : foreign key from device
	 * @param command_id : foreign key from command
	 * @return query to link device and command
	 */
	@Override
	public String getQueryForSetLinkDevCom(int dev_id, int command_id){
		String query = "";
		
		query = "INSERT INTO link_device_command(device_id_fk, command_id_fk) " +
				"VALUES ("+ dev_id + ", " + command_id + ");";
		
		return query;
	}
	
	/**
	 * function to get the query for linling a system device with a controlscript
	 * 
	 * @param cs_id : foreign key from controlscript
	 * @param sys_dev : foreign key from sys_device
	 * @return query to link sys_device and controlscript
	 */
	@Override
	public String getQueryForSetConnectDevices(int sys_dev, int cs_id){
		String query = "";
		
		query = "INSERT INTO connected_devices(sys_dev_id_fk, cs_id_fk) "+
				"VALUES ("+ sys_dev + ", " + cs_id + ");";
		
		return query;
	}
	
	/**
	 * function to get the query for setting new command
	 * 
	 * @param id : primary key
	 * @param command : name of the command
	 * @param attr : attribute to control
	 * @param response : if command has to send a response
	 * @return query to set a new command
	 */
	@Override
	public String getQueryForSetCommand(int id, String command, String attr, String response){
		String query = "";
		
		query = "INSERT INTO command (command_id, command, attr, response) " +
				"VALUES (" + id + ", '" +  command + "', '" + attr + "', '" + response + "');";
		
		return query;
	}
	
	/**
	 * function to get the query for setting control script
	 * 
	 * @param id : primary key
	 * @param command_id : foreign key of table command
	 * @param sys_dev_id : foreign key of table sys_device
	 * @param value : value to set for specified (by command) attribute
	 * @param cs_time : if there is a special time for command
	 * @param next_script : id of the following script (if there is one)
	 * @return query to set a new controlscript
	 */
	@Override
	public String getQueryForSetControlscript(int id, int command_id, int sys_dev_id, String value, String cs_time, int next_script){
		String query = "";
		
		query = "INSERT INTO controlscript (cs_id, command_id_fk, sys_dev_id_fk, value, cs_time, nextscript) " +
				"VALUES (" + id + ", " + command_id + ", " + sys_dev_id + ", '" + value + "', '" + cs_time + "', " + next_script + ");";
		
		return query;
	}
	
	/**
	 * function to get the query to update room data by roomId
	 * 
	 * @param roomId : Primary Key from table Floor
	 * @param floorId: foreign key of floor_id_fk
	 * @param description: description of the room
	 * @return query to update room data
	 */
	@Override
	public String getQueryForUpdateRoom(int roomId, int floorId, String description){
		String query = "";
		
		query = "UPDATE room SET " +
				"floor_id_fk = " + floorId +
				" , r_description = '" + description  +
				"' WHERE room_id = " + roomId + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query to update a floors data
	 * 
	 * @param floorId : Primary Key from table Floor
	 * @param floorNum: number of floor
	 * @param description: description of the floor
	 * @return query to update floor data
	 */
	@Override
	public String getQueryForUpdateFloor(int floorId, int floorNum, String description){
		String query = "";
		
		query = "UPDATE floor SET " +
				"f_num = " + floorNum +
				" , f_description = '" + description  +
				"' WHERE floor_id = " + floorId + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query for updating radio type
	 * 
	 * @param id : primary key
	 * @param name : name of the radio type e.g. Ant
	 * @return query to update radio type
	 */
	@Override
	public String getQueryForUpdateRadioType(int id, String name){
		String query = "";
		
		query = "UPDATE radiotype SET " +
				"rt_name = '" + name  +
				"' WHERE rt_id = " + id + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query for updating device
	 * 
	 * @param id : primary key
	 * @param rt_id : foreign key from table radiotype
	 * @param model_type : type/name of device
	 * @param manufacture : manufacture of device
	 * @param specific : specification of device e.g Scale
	 * @param response : response for device if needed
	 * @param attr : attributes of device 
	 * @return query to update device
	 */
	@Override
	public String getQueryForUpdateDevice(int id, int rt_id, String model_type, String manufacture, String specific, String response, String[] attr){
		String query = "";
		
		query = "UPDATE device SET " +
				"rt_id_fk = " + rt_id + ", " +
				"model_type = '" + model_type + "', " +
				"manufacture = '" + manufacture + "', " +
				"d_specific = '" + specific + "', " +
				"response = '" + response + "' ";
		
				for(int i = 0; i < attr.length; i++){
					query += ", attr_" + (i+1) + " = '" + attr[i] + "' ";
				}
				query += " WHERE device_id = " + id + " ;";
		
		return query;
	}
		
	/**
	 * function to get the query to update spot data
	 * 
	 * @param id : Primary Key from table Floor
	 * @param room_id :  foreign key from table room
	 * @param ip_address: ip address of the spot
	 * @return query to update spot data
	 */
	@Override
	public String getQueryForUpdateSpot(int id, int room_id, String ip_address){
		String query = "";
		
		query = "UPDATE spot SET " +
				"room_id_fk = " + room_id +
				" , ip_address = '" + ip_address  +
				"' WHERE spot_id = " + id + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query for updating a command
	 * 
	 * @param id : primary key
	 * @param command : name of the command
	 * @param attr : attribute to control
	 * @param response : if command has to send a response
	 * @return query to update a command
	 */
	@Override
	public String getQueryForUpdateCommand(int id, String command, String attr, String response){
		String query = "";
		
		query = "UPDATE command SET " +
				"command = '" + command +
				"' , attr = '" + attr +
				"' , response = '" + response +
				"' WHERE command_id = " + id + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query for updating a control script
	 * 
	 * @param id : primary key
	 * @param command_id : foreign key of table command
	 * @param sys_dev_id : foreign key of table sys_device
	 * @param value : value to set for specified (by command) attribute
	 * @param cs_time : if there is a special time for command
	 * @param next_script : id of the following script (if there is one)
	 * @return query to update a controlscript
	 */
	@Override
	public String getQueryForUpdateControlscript(int id, int command_id, int sys_dev_id, String value, String cs_time, int next_script){
		String query = "";
		
		query = "UPDATE controlscript SET " +
				"command_id_fk = " + command_id +
				", sys_dev_id_fk = " + sys_dev_id +
				", value = '" + value +
				"' , cs_time = '" + cs_time +
				"' , nextscript = " + next_script +
				" WHERE cs_id = " + id + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query to delete floor by Id
	 * 
	 * @param floorId : Primary Key from table floor
	 * @return query to delete floor
	 */
	@Override
	public String getQueryForDeleteFloor(int floorId){
		String query = "";
		
		query = "DELETE FROM floor WHERE spot_id = "+ floorId +" ;";
		
		return query;
	}
	
	/**
	 * function to get the query to delete radio type by Id
	 * 
	 * @param rt_Id : Primary Key from table radiotype
	 * @return query to delete radiotype
	 */
	@Override
	public String getQueryForDeleteRadiotype(int rt_id){
		String query = "";
		
		query = "DELETE FROM radiotype WHERE rt_id = " + rt_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to delete device by Id
	 * 
	 * @param dev_Id : Primary Key from table device
	 * @return query to delete device
	 */
	@Override
	public String getQueryForDeleteDevice(int dev_id){
		String query = "";
		
		query = "DELETE FROM device WHERE device_id = " + dev_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to delete room by Id
	 * 
	 * @param roomId : Primary Key from table room
	 * @return query to delete room
	 */
	@Override
	public String getQueryForDeleteRoom(int roomId){
		String query = "";
		
		query =  "DELETE FROM room WHERE spot_id = "+ roomId +" ;";
		
		return query;
	}
	
	/**
	 * function to get the query to delete spot by Id
	 * 
	 * @param spot_id : Primary Key from table spot
	 * @return query to delete spot
	 */
	@Override
	public String getQueryForDeleteSpot(int spot_id){
		String query = "";
		
		query = "DELETE FROM spot WHERE spot_id = "+ spot_id +" ;";
		
		return query;
	}
	
	/**
	 * function to get the query to delete command by Id
	 * 
	 * @param command_id : Primary Key from table command
	 * @return query to delete command
	 */
	@Override
	public String getQueryForDeleteCommand(int command_id){
		String query = "";
		
		query = "DELETE FROM command WHERE command_id = " + command_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to delete system deive by Id
	 * 
	 * @param sys_dev_id : Primary Key from table sys_device
	 * @return query to delete sys_device
	 */
	@Override
	public String getQueryForDeleteSysDevice(int sys_dev_id){
		String query = "";
		
		query = "DELETE FROM sys_device WHERE sys_dev_id = " + sys_dev_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to delete control script by Id
	 * 
	 * @param cs_id : Primary Key from table controlscript
	 * @return query to delete controlscript
	 */
	@Override
	public String getQueryForDeleteControlScript(int cs_id){
		String query = "";
		
		query = "DELETE FROM controlscript WHERE cs_id = " + cs_id + ";";
		
		return query;
	}
	
	/**
	 * function to get the query to delink connected devices
	 * 
	 * @param sys_dev_id : foreign Key from table system device
	 * @param cs_id : foreign key from table controlscript
	 * @return query to delete connected_devices
	 */
	@Override
	public String getQueryForDeleteConnDevs(int sys_dev_id, int cs_id){
		String query = "";
		
		if(cs_id == 0 && sys_dev_id != 0){
			query = "DELETE FROM connected_devices WHERE sys_dev_id_fk = " + sys_dev_id + ";";
		}else if (sys_dev_id == 0 && cs_id != 0){
			query = "DELETE FROM connected_devices WHERE cs_id_fk = " + cs_id + ";";
		}else if (sys_dev_id != 0 && cs_id != 0){
			query = "DELETE FROM connected_devices WHERE " +
					"sys_dev_id_fk = " + sys_dev_id + " AND " +
					"cs_id_fk = " + cs_id + ";";
		}
		
		return query;
	}
	
	/**
	 * function to get the query to delink linked devices with commands 
	 * 	  
	 * @param dev_id : foreign key from table device
	 * @param com_id : foreign key from table command
	 * @return query to delete link_device_command
	 */
	@Override
	public String getQueryForDeleteLinkDevCom(int dev_id, int com_id){
		String query = "";
		
		if(com_id == 0 && dev_id != 0){
			query = "DELETE FROM link_device_command WHERE device_id_fk = " + dev_id + ";";
		}else if (dev_id == 0 && com_id != 0){
			query = "DELETE FROM link_device_command WHERE command_id_fk = " + com_id + ";";
		}else if (dev_id != 0 && com_id != 0){
			query = "DELETE FROM link_device_command WHERE " +
					"device_id_fk = " + dev_id + " AND " +
					"command_id_fk = " + com_id + ";";
		}
		return query;
	}
}
